﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using Mk.CommonMethod;
using System.Data;

namespace Dashboard.Expense.Business
{
    public class BusinessBase
    {
        protected readonly DBConnection DbConnection;
        public BusinessBase()
        {
            DbConnection = new DBConnection
                                {
                                    ConnectionString = ConfigurationManager.AppSettings["ConnectionString"],
                                    ConnectionType = ConfigurationManager.AppSettings["ConnectionType"]
                                };
        }

        protected string FormatStringForSqlVariable(string inputString)
        {
            if (!string.IsNullOrEmpty(inputString))
            {
                return inputString.Replace("'", "''");
            }  
            return "";
        }

        public DataTable GetAllReasonList()
        {
            string sql = "SELECT ptype,pcode,pdesc,pvalue1,pvalue2 FROM NonworkingReason WHERE ptype='ReasonCode' ORDER BY convert(int,pcode,0)";

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetReasonInfo(string reasonCode)
        {
            string sql = "SELECT * FROM NonworkingReason WHERE ptype='ReasonCode' AND pcode='" + this.FormatStringForSqlVariable(reasonCode) + "'";

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetStaffOfTeam(string team)
        {
            string sql = "select * from namelist where team='" + team + "' order by shift,staffno";
            return DbConnection.DataSet(sql).Tables[0];
        }
        public DataTable GetStaffNo()
        {
            string sql = "select staffno from namelist order by staffno";
            return DbConnection.DataSet(sql).Tables[0];
        }
        public DataTable GetStaffOfTeamShift(string team, string shift)
        {
            string sql = "select * from namelist where team='" + team + "' and shift='" + shift + "' order by staffno";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable LoadWorkingShift()
        {
            string sql = "select id,code,description from WorkingShift where IsValid='Y' order by id";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable LoadTeamList()
        {
            string sql = "select distinct team from namelist order by team";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable LoadTeamShiftList(string team)
        {
            string sql = "select distinct shift from namelist where team='" + team + "' order by shift";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetStaffInfo(string staffNo)
        {
            string sql = "select * from namelist where staffno='" + this.FormatStringForSqlVariable(staffNo) + "'";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public string GetBaseCurrency()
        {
            string sql = "select * from EXP_parameter where ptype='BaseCurrency' and pcode='BaseCurrency'";
            DataTable param = DbConnection.DataSet(sql).Tables[0];
            if (param.Rows.Count == 0)
            {
                return "RMB";
            }
            else
            {
                return param.Rows[0]["pvalue1"].ToString();
            }
        }

        public DataTable GetCurrencies()
        {
            string sql = "select * from EXP_parameter where ptype='Currency' order by pcode";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetLocations()
        {
            string sql = "select * from EXP_parameter where ptype='Location' order by pcode";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public bool LocationExists(string location)
        {
            string sql = "select count(*) from EXP_parameter where ptype='Location' and pcode='" + location.Replace("'", "''") + "'";
            DataTable local = DbConnection.DataSet(sql).Tables[0];

            if (int.Parse(local.Rows[0][0].ToString()) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public void AddLocation(string location)
        {
            location = location.Replace("'", "''");
            string sql = @"insert into EXP_parameter(ptype,pcode,pdesc,pvalue1,pvalue2,pvalue3) 
                           values ('Location','" + location + "','" + location + "','" + location + "',null,null)";
            DbConnection.ExecuteSql(sql);
        }
    }
}
